using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Text;

public partial class Pages_Statement : System.Web.UI.Page
{
    private SqlConnection _oleConn;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            TextBox_fromdate.Text = DateTime.Today.Year.ToString();
            TextBox_todate.Text = DateTime.Today.ToString();
        }
    }
       
    protected void Button1_Click1(object sender, EventArgs e)
    {

        GridView2.DataSource = null;
        GridView2.DataBind();
        try
        {
            
                TextBox1.Text = " ";

                TextBox_fromdate.Text = "01/01/" + DropDownList3.SelectedValue.ToString(); // prepare a start date from the year entered

 //               string cmd = "(select date,nature,refn,totalton,amount as credit, 0 as debit, 0 as balance  from trade where date>='" + TextBox_fromdate.Text + "' and date<='" + TextBox_todate.Text + "' and cracctype=" + DropDownList1.SelectedValue.Trim() + " and creditacc=" + DropDownList2.SelectedValue.Trim() + " union select date,nature,refn,totalton,0 as credit,amount as debit, 0 as balance  from trade where date>='" + TextBox_fromdate.Text + "' and date<='" + TextBox_todate.Text + "' and debacctype=" + DropDownList1.SelectedValue.Trim() + " and debitacc=" + DropDownList2.SelectedValue.Trim() + " union SELECT  '" + TextBox_fromdate.Text + "' as date,'B/F' as nature,0 as refn,0 as totalton, 0 as debit, 0 as credit, openbalance as amount from accopen where acctype =" + DropDownList1.SelectedValue.Trim() + " and acccode=" + DropDownList2.SelectedValue.Trim() + " and opendate='" + TextBox_fromdate.Text + "')order by date";
                string cmd = "(select date,nature,refn,totalton,amount as credit, 0 as debit, 0 as balance, explain, debexp  from trade where date>='" + TextBox_fromdate.Text + "' and date<='" + TextBox_todate.Text + "' and cracctype=" + Common.accTypeValue + " and creditacc=" + Common.accCodeValue + " union select date,nature,refn,totalton,0 as credit,amount as debit, 0 as balance, explain, debexp  from trade where date>='" + TextBox_fromdate.Text + "' and date<='" + TextBox_todate.Text + "' and debacctype=" + Common.accTypeValue + " and debitacc=" + Common.accCodeValue + " union SELECT  '" + TextBox_fromdate.Text + "' as date,'B/F' as nature,0 as refn,0 as totalton, 0 as debit, 0 as credit, openbalance as amount,' ' as explain,' ' as debexp from accopen where acctype =" + Common.accTypeValue + " and acccode=" + Common.accCodeValue + " and opendate='" + TextBox_fromdate.Text + "')order by date";
             

                DataSet ds = new DataSet();
                Common.Getdata(ds, cmd, "GL");
                ds.Tables["GL"].Columns.Add("Particulars",typeof(string));
                ds.Tables["GL"].Columns.Add("Date1", typeof(string));

                int i;
                

                ds.Tables["GL"].Rows[0]["date1"] = Convert.ToDateTime(ds.Tables["GL"].Rows[0]["date"]).ToString("dd/MM/yyyy");
                // Calculate and Update balance field
                for (i = 1; i < ds.Tables["GL"].Rows.Count; i++)
                {
                    ds.Tables["GL"].Rows[i]["balance"] = Convert.ToDecimal(ds.Tables["GL"].Rows[i - 1]["balance"]) - Convert.ToDecimal(ds.Tables["GL"].Rows[i]["credit"]) + Convert.ToDecimal(ds.Tables["GL"].Rows[i]["debit"]);
                    ds.Tables["GL"].Rows[i]["date1"] = Convert.ToDateTime(ds.Tables["GL"].Rows[i]["date"]).ToString("dd/MM/yyyy");
                }
                

                GridView2.DataSource = ds.Tables["GL"]; 
                GridView2.DataBind();
                
                
            
        }
        catch (Exception Ex)
        {
            TextBox1.Text = "Wrong Input of date(possibly)";
            
        }
    }




}
